﻿using System;
using System.ComponentModel;
using System.Data;
using System.Web.Services;
using System.IO;
using Shuba.Model;
using System.Data.Common;
using System.Configuration;
using System.IO.Compression;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for enh1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class enh1 : System.Web.Services.WebService
    {
        string AppCode = ConfigurationManager.AppSettings["app.code.spada"];
  
        private UserInfo Get(string nip, string AppCode)
        {
            UserInfo user = new UserInfo();
            Database db = DBConn.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_ALL_USER @NIP, @APP_ID";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), dr.GetString(8) == "1" ? true : false, dr.GetString(9) == "1" ? true : false , dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }

        [WebMethod]
        public void AddLog(string NIP, string IPAddress, bool isLogin, string PCName)
        {
            try
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd;
                if (isLogin)
                {
                    UserInfo info = Get(NIP, AppCode);
                    info.IsLogin = true;
                    info.LoginAddr = IPAddress;
                    info.LoginDate = DateTime.Now;

                    //update user
                    cmd = db.GetStoredProcCommand("WS_UserUpdate_ENH2B3");
                    db.AddInParameter(cmd, "@USER_ID", DbType.Int32, info.Id);
                    db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                    db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                    db.AddInParameter(cmd, "@IS_LOGIN", DbType.String, "1");
                    db.AddInParameter(cmd, "@LOGIN_ADDRESS", DbType.String, info.LoginAddr);
                    db.AddInParameter(cmd, "@PC_NAME", DbType.String, PCName);

                    db.ExecuteNonQuery(cmd);

                    //add translog

                    cmd = db.GetStoredProcCommand("ADD_TRANS_LOG");
                    db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                    db.AddInParameter(cmd, "@USER_NAME", DbType.String, NIP);
                    db.AddInParameter(cmd, "@TRANS_TYPE", DbType.String, "Login");
                    db.AddInParameter(cmd, "@TRANS_DESC", DbType.String, String.Format("Login To {0} Application, User Name : {1}, Remote Address: {2}", AppCode, NIP, IPAddress));

                    db.ExecuteNonQuery(cmd);
                }
                else
                {
                    //update user
                    UserInfo info = Get(NIP, AppCode);
                    cmd = db.GetStoredProcCommand("WS_UserUpdate_ENH2B3");
                    db.AddInParameter(cmd, "@USER_ID", DbType.Int32, info.Id);
                    db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                    db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                    db.AddInParameter(cmd, "@IS_LOGIN", DbType.String, "0");
                    db.AddInParameter(cmd, "@LOGIN_ADDRESS", DbType.String, info.LoginAddr);
                    db.AddInParameter(cmd, "@PC_NAME", DbType.String, PCName);

                    db.ExecuteNonQuery(cmd);

                    //add translog
                    cmd = db.GetStoredProcCommand("ADD_TRANS_LOG");
                    db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                    db.AddInParameter(cmd, "@USER_NAME", DbType.String, NIP);
                    db.AddInParameter(cmd, "@TRANS_TYPE", DbType.String, "Login");
                    db.AddInParameter(cmd, "@TRANS_DESC", DbType.String, String.Format("Logout To {0} Application, User Name : {1}, Remote Address: {2}", AppCode, NIP, IPAddress));

                    db.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex) 
            {
                CreateErrorLog("enh1 AddLog : " + ex.Message.ToString(), true);
            }
        }

        [WebMethod]
        public bool CheckService()
        {
            return true;
        }

        [WebMethod]
        public string GeneratePRNNew(string unitCode, string segmentCode, string prospectType, string debtor_type, string channeling, string CustName, string IsNegativeChecking)
        {
            string prn = "";

            try
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("GENERATE_PRN_NEW");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode.Trim());
                db.AddInParameter(cmd, "@SEGMENT_CODE", DbType.String, segmentCode.Trim());
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospectType.Trim());
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, debtor_type.Trim());
                db.AddInParameter(cmd, "@CHANNEL_ID", DbType.String, channeling.Trim());
                db.AddInParameter(cmd, "@NAM_CUST", DbType.String, CustName);
                db.AddInParameter(cmd, "@IS_CHECK_NEGATIVE_FILE", DbType.String, IsNegativeChecking.Trim());

                cmd.CommandTimeout = 1800;
                prn = db.ExecuteScalar(cmd).ToString();
            }
            catch (Exception ex)
            {
                CreateErrorLog("enh1 GeneratePRNNew : " + ex.Message.ToString(), true);
            }
            return prn;
        }

        [WebMethod]
        public byte[] GetBranchByCode(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand("SELECT BRANCH_ID, BRANCH_NAME FROM TBL_MS_BRANCH (NOLOCK) WHERE LTRIM(RTRIM(BRANCH_ID)) = @UNIT_CODE");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }
        
        [WebMethod]
        public byte[] GetBranchZIPAll(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, ZIP_CODE, KODYA_KAB, KECAMATAN, KELURAHAN, LAYER, QUADRANT, IS_DELETED
		        FROM TBL_MS_BRANCH_ZIP (NOLOCK) WHERE LTRIM(RTRIM(BRANCH_ID)) = @UNIT_CODE");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public DateTime GetServerDateTime()
        {
            return DateTime.Now;
        }

        [WebMethod]
        public byte[] GetSPADAUserMenu(string nip, string pass, string unit, DateTime updateDate)
        {
            if (IsValidUser(nip, pass))
            {
                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("GET_SPADA_USER_MAIN_MENU_NEW");

                db.AddInParameter(cmd, "@APP_ID", DbType.String, SPADAAppCode);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);
                DataSet dsMainMenu = db.ExecuteDataSet(cmd);
                dsMainMenu.Tables[0].TableName = "TBL_MENU";

                cmd = db.GetStoredProcCommand("GET_SPADA_USER_MENU_NEW");
                db.AddInParameter(cmd, "@APP_ID", DbType.String, SPADAAppCode);
                db.AddInParameter(cmd, "@NIP", DbType.String, nip);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, unit);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);
                DataSet dsUserMenu = db.ExecuteDataSet(cmd);
                dsUserMenu.Tables[0].TableName = "TBL_USER_MENU";

                cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ROW_ID, GU_FUP, GU_CLOSE_DEAL, PURGING_PERIOD, 
                WEB_ROOT_ADDRESS, DB_PASS_NEW, DB_PASS_OLD, NPWP, TIME_OUT, IS_NEED_FINAL_DEDUP 
                FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
                
                DataSet dsSysParams = db.ExecuteDataSet(cmd);
                dsSysParams.Tables[0].TableName = "TBL_SYS_PARAMS";

                using (DataSet ds = new DataSet())
                {
                    DataTable dt1 = dsMainMenu.Tables[0].Clone();
                    foreach (DataRow dr in dsMainMenu.Tables[0].Rows)
                    {
                        dt1.ImportRow(dr);
                    }

                    DataTable dt2 = dsUserMenu.Tables[0].Clone();
                    foreach (DataRow dr in dsUserMenu.Tables[0].Rows)
                    {
                        dt2.ImportRow(dr);
                    }

                    DataTable dt3 = dsSysParams.Tables[0].Clone();
                    foreach (DataRow dr in dsSysParams.Tables[0].Rows)
                    {
                        dt3.ImportRow(dr);
                    }

                    ds.Tables.Add(dt1);
                    ds.Tables.Add(dt2);
                    ds.Tables.Add(dt3);
                    return TransformIntoBytes(ds);
                }
            }
            return null;
        }

        [WebMethod]
        public byte[] GetStandard(string nip, string pass, string unit, DateTime update)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetStandard_ENH2B3");

                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, update);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                DataSet dsStandard = db.ExecuteDataSet(cmd);
                dsStandard.Tables[0].TableName = "TBL_MS_STANDARD";
                dsStandard.Tables[1].TableName = "TBL_MS_BRANCH";
                dsStandard.Tables[2].TableName = "TBL_BRANCH_TARGET_PLAN";
                dsStandard.Tables[3].TableName = "TBL_MS_BRANCH_ZIP";
                dsStandard.Tables[4].TableName = "TBL_MS_DOCUMENTS";
                dsStandard.Tables[5].TableName = "TBL_MS_DOCUMENTS_FUNDING";
                dsStandard.Tables[6].TableName = "TBL_MS_PRODUCTS";
                dsStandard.Tables[7].TableName = "TBL_DOCUMENT_PRODUCT";

                return TransformIntoBytes(dsStandard);
            }
            return null;
        }

        [WebMethod]
        public byte[] GetStandard2(string nip, string pass, string unit, DateTime update)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetStandard2_ENH2B3");

                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, update);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                DataSet dsStandard = db.ExecuteDataSet(cmd);
                dsStandard.Tables[0].TableName = "TBL_MS_CHANNEL";
                dsStandard.Tables[1].TableName = "TBL_MS_EXTERNAL_MAPPING";
                dsStandard.Tables[2].TableName = "TBL_MS_HOLIDAY";
                dsStandard.Tables[3].TableName = "TBL_MS_FLAG_FREEZE";

                return TransformIntoBytes(dsStandard);
            }
            return null;
        }

        [WebMethod]
        public byte[] GetUpdateTableSpada(string createDate)
        {
            Database db = DBConn.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [TABLE_NAME]
                  ,[FIELD_NAME]
                  ,[DATA_TYPE]
                  ,[IS_TABLE_NEW]
                  ,[ENTRY_DATE]
              FROM [TBL_UPDATE_TBL_SPADA] (NOLOCK) WHERE ENTRY_DATE > DATEADD(DAY, -10, @ENTRY_DATE)");

            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, createDate);

            return TransformIntoBytes(db.ExecuteDataSet(cmd));
        }

        [WebMethod]
        public byte[] GetUsers(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT USER_ID, LTRIM(RTRIM(AO_CODE)) AS AO_CODE, NIP, BRANCH_ID, USER_NAME, 
                       USER_TYPE, EMAIL_ADDRESS, MAC_ADDRESS, OLD_MAC_ADDRESS, IS_MIGRATION, AO_TYPE 
                FROM V_USER (NOLOCK) WHERE (LTRIM(RTRIM(BRANCH_ID)) = @UNIT_CODE) AND (APP_ID = @APP_ID) ");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetUsersCO(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT USER_ID, LTRIM(RTRIM(AO_CODE)) AS AO_CODE, NIP, BRANCH_ID, 
                        USER_NAME, USER_TYPE, EMAIL_ADDRESS, MAC_ADDRESS, OLD_MAC_ADDRESS 
                 FROM V_USER (NOLOCK) WHERE (LTRIM(RTRIM(BRANCH_ID)) = @UNIT_CODE) 
                        AND (LTRIM(RTRIM(USER_TYPE)) ='CO') 
                 GROUP BY USER_ID, AO_CODE, NIP, BRANCH_ID, USER_NAME, USER_TYPE, EMAIL_ADDRESS, 
                        MAC_ADDRESS, OLD_MAC_ADDRESS ");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }
                
        static bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
        }

        CompressionMode compress = CompressionMode.Compress;

        private byte[] TransformIntoBytes(DataSet ds)
        {
            using (MemoryStream memStream = new MemoryStream())
            {
                byte[] data;
                using (GZipStream zipStream = new GZipStream(memStream, compress))
                {
                    ds.WriteXml(zipStream, XmlWriteMode.WriteSchema);
                    zipStream.Close();
                }
                data = memStream.ToArray();
                memStream.Close();
                return data;
            }
        }

        void CreateErrorLog(string text, bool isnewline)
        {
            try
            {
                string appPath = Server.MapPath("~/AppLog");
                if (appPath.Substring(appPath.Length - 1, 1) == "\\")
                {
                    appPath = appPath.Substring(0, appPath.Length - 1);
                }

                if (File.Exists(String.Format("{0}\\Log.txt", appPath)))
                {
                    using (StreamWriter file = new StreamWriter(String.Format("{0}\\Log.txt", appPath), true))
                    {
                        if (!isnewline) file.WriteLine();
                        file.WriteLine(String.Format("{0}", text));
                    }
                }
                else
                {
                    File.WriteAllText(String.Format("{0}\\Log.txt", appPath), text);
                }
            }
            catch (Exception) { }
        }
    }
}